﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class StaffDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSql = null;

        public DataTable getAllStaff() {
            strSql = "SELECT Staff.staff_id, Staff.staff_name, Staff.staff_address";
            strSql+= ", Staff.staff_phone, Staff.staff_username, Staff.staff_password";
            strSql+=", Staff.role_id, Role.role_name FROM Role INNER JOIN Staff ON Role.role_id = Staff.role_id";
            cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public DataTable getStaff(int id)
        {
            strSql = "SELECT Staff.staff_id, Staff.staff_name, Staff.staff_address";
            strSql += ", Staff.staff_phone, Staff.staff_username, Staff.staff_password";
            strSql += ", Staff.role_id, Role.role_name FROM Role INNER JOIN Staff ON Role.role_id = Staff.role_id";
            strSql += " Where Staff.staff_id = @id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id",SqlDbType.Int).Value=id;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public void insertStaff(string name, string add, string phone, string username, string pass, int roleId) {
            strSql = "Insert Into Staff(staff_name,staff_address,staff_phone,staff_username,staff_password,role_id)";
            strSql += " Values(@name,@add,@phone,@user,@pass,@roleId)";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = name;
            cmd.Parameters.Add("add", SqlDbType.NText).Value = add;
            cmd.Parameters.Add("phone", SqlDbType.NText).Value = phone;
            cmd.Parameters.Add("user", SqlDbType.NText).Value = username;
            cmd.Parameters.Add("pass", SqlDbType.Text).Value = pass;
            cmd.Parameters.Add("roleId", SqlDbType.Int).Value = roleId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void updateStaff(int id,string name, string add, string phone, string username, string pass, int roleId)
        {
            strSql = "Update Staff Set staff_name=@name,staff_address=@add,staff_phone=@phone";
            strSql+=",staff_username=@user,staff_password=@pass,role_id=@roleId";
            strSql += " Where staff_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = name;
            cmd.Parameters.Add("add", SqlDbType.NText).Value = add;
            cmd.Parameters.Add("phone", SqlDbType.NText).Value = phone;
            cmd.Parameters.Add("user", SqlDbType.NText).Value = username;
            cmd.Parameters.Add("pass", SqlDbType.Text).Value = pass;
            cmd.Parameters.Add("roleId", SqlDbType.Int).Value = roleId;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = id;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void deleteStaff(int id) {
            cmd = new OleDbCommand("Delete From Staff Where staff_id = @id");
            cmd.Parameters.Add("id", SqlDbType.Int).Value = id;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
    }
}
